package skladiscenje;

import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

public class Evidenca {

	public static Blago getBlagoById(int ID) {
		String query = "SELECT Blago.ID, Blago.ime, Blago.tip, Blago.kolicina, Blago.enota, Blago.sektor, Blago.dovoljenaKolicina, enotaBlaga.imeEnote, tipBlaga.imeTipa " +
		   			   "FROM tipBlaga " +
		   			   "INNER JOIN (enotaBlaga INNER JOIN Blago ON enotaBlaga.[ID] = Blago.[enota]) ON tipBlaga.[ID] = Blago.[tip]" +
		   			   "WHERE Blago.ID = ?;";
		try {
			Conn conn = new Conn();
			PreparedStatement prepStmt = conn.getC().prepareStatement(query);
			prepStmt.setInt(1, ID);
			ResultSet rs = prepStmt.executeQuery();
			
			if (rs.next()) {
				Blago blago = new Blago();
				
				blago.setID(rs.getInt("ID"));
				blago.setIme(rs.getString("ime"));
				blago.setTip(rs.getInt("tip"));
				blago.setImeTip(rs.getString("imeTipa"));
				blago.setKolicina(rs.getInt("kolicina"));
				blago.setEnota(rs.getInt("enota"));
				blago.setImeEnota(rs.getString("imeEnote"));
				blago.setSektor(rs.getInt("sektor"));
				blago.setDovoljenaKolicina(rs.getInt("dovoljenaKolicina"));
				
				conn.zapri();
				return blago;
			} else {
				conn.zapri();
				return null;
			}
		} catch (SQLException e) {
			return null;
		}	
	}

	public static void dodajBlago(String ime, int tip, int kolicina, int enota, int sektor, int dovoljenaKolicina) {
		String query = "INSERT INTO Blago (ime, tip, kolicina, enota, sektor, dovoljenaKolicina) VALUES (?, ?, ?, ?, ?, ?);";
		Conn conn = new Conn();
		try{
			PreparedStatement ps=conn.getC().prepareStatement(query);
			ps.setString(1, ime);
			ps.setInt(2, tip);
			ps.setInt(3, kolicina);
			ps.setInt(4, enota);
			ps.setInt(5, sektor);
			ps.setInt(6, dovoljenaKolicina);
			ps.executeUpdate();
			ps.close();
		}catch(SQLException e){}
		conn.zapri();
	}

	public static void posodobitevBlaga(int id, String ime, int tip, int kolicina, int enota, int sektor, int dovoljenaKolicina) {
		String query = "UPDATE Blago SET ime = ?, tip = ?, kolicina = ?, enota = ?, sektor = ?, dovoljenaKolicina = ? WHERE ID = ?;";
		Conn conn = new Conn();
		try{
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setString(1, ime);
			ps.setInt(2, tip);
			ps.setInt(3, kolicina);
			ps.setInt(4, enota);
			ps.setInt(5, sektor);
			ps.setInt(6, dovoljenaKolicina);
			ps.setInt(7, id);
			ps.executeUpdate();
			ps.close();
		}catch(SQLException e){}
		conn.zapri();
	}

	public static void brisiBlago(int id) {
		String query = "DELETE FROM Blago WHERE ID = ?;";
		Conn conn = new Conn();
		try{
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {}
		conn.zapri();
	}

	public static void prevzemIzdajaBlaga(int ID, int kolicina) {
		Conn conn = new Conn();
		try {
			String query = "UPDATE Blago SET kolicina = ? WHERE ID = ?;";
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setInt(1, kolicina);
			ps.setInt(2, ID);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			conn.zapri();
		}
		conn.zapri();
	}

	public static Uporabnik getUporabnikByID(int ID) {
		String query = "SELECT * FROM Uporabnik WHERE ID = ?;";
		Uporabnik uporabnik;
		try {
			Conn conn = new Conn();
			PreparedStatement prepStmt = conn.getC().prepareStatement(query);
			prepStmt.setInt(1, ID);
			ResultSet rs = prepStmt.executeQuery();
			
			if (rs.next()) {
				int pravice = rs.getInt("pravice");
				
				if(pravice == 2){
					uporabnik = new Skladiscnik();
					uporabnik.setSektor(rs.getInt("sektor"));
				}
				else if(pravice == 1)
					uporabnik = new VodjaSkladisca();
				else
					uporabnik = new Uporabnik();
				
				uporabnik.setID(rs.getInt("ID"));
				uporabnik.setIme(rs.getString("ime"));
				uporabnik.setPriimek(rs.getString("priimek"));
				uporabnik.setUporabniskoIme(rs.getString("uIme"));
				uporabnik.setGeslo(rs.getString("geslo"));
				uporabnik.setRacun(rs.getString("racun"));
				uporabnik.setEmail(rs.getString("email"));
				uporabnik.setNaslov(rs.getString("naslov"));
				uporabnik.setPravice(pravice);
				
				conn.zapri();
				return uporabnik;
			} else {
				conn.zapri();
				return null;
			}
		} catch (SQLException e) {
			return null;
		}
	}

	public static String preveriUporabnika(String uime, String geslo) {
		String query = "SELECT * FROM Uporabnik WHERE uIme = ? AND geslo = ?;";
		int id;
		try {
			Conn conn = new Conn();
			PreparedStatement prepStmt = conn.getC().prepareStatement(query);
			prepStmt.setString(1, uime);
			prepStmt.setString(2, geslo);
			ResultSet rs = prepStmt.executeQuery();
			
			if (rs.next()) {
				id = rs.getInt("ID");
				conn.zapri();
				return Integer.toString(id);
			} else {
				conn.zapri();
				return "failed";
			}
		} catch (SQLException e) {
			return "failed";
		}
	}

	public static void novSkladiscnik(String ime, String priimek, String uime, String geslo, String racun, String email, String naslov, int sektor) {
		String query = "INSERT INTO Uporabnik (ime, priimek, uIme, geslo, racun, email, naslov, pravice, sektor) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
		Conn conn = new Conn();
		int pravice = 2;
		try{
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setString(1, ime);
			ps.setString(2, priimek);
			ps.setString(3, uime);
			ps.setString(4, geslo);
			ps.setString(5, racun);
			ps.setString(6, email);
			ps.setString(7, naslov);
			ps.setInt(8, pravice);
			ps.setInt(9, sektor);
			ps.executeUpdate();
		}catch(SQLException e){}
		conn.zapri();
	}

	public static void posodobitevSkladiscnika(int id, String ime, String priimek, String uime, String geslo, String racun, String email, String naslov, int sektor) {
		String query = "UPDATE Uporabnik SET ime = ?, priimek = ?, uIme = ?, geslo = ?, racun = ?, email = ?, naslov = ?, pravice = ?, sektor = ? WHERE ID = ?;";
		Conn conn = new Conn();
		int pravice = 2;
		try{
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setString(1, ime);
			ps.setString(2, priimek);
			ps.setString(3, uime);
			ps.setString(4, geslo);
			ps.setString(5, racun);
			ps.setString(6, email);
			ps.setString(7, naslov);
			ps.setInt(8, pravice);
			ps.setInt(9, sektor);
			ps.setInt(10, id);
			ps.executeUpdate();
		}catch(SQLException e){}
		conn.zapri();
	}

	public static void novVodja(String ime, String priimek, String uime, String geslo, String racun, String email, String naslov) {
		String query = "INSERT INTO Uporabnik (ime, priimek, uIme, geslo, racun, email, naslov, pravice, sektor) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
		Conn conn = new Conn();
		int pravice = 1;
		int sektor = 0;
		try{
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setString(1, ime);
			ps.setString(2, priimek);
			ps.setString(3, uime);
			ps.setString(4, geslo);
			ps.setString(5, racun);
			ps.setString(6, email);
			ps.setString(7, naslov);
			ps.setInt(8, pravice);
			ps.setInt(9, sektor);
			ps.executeUpdate();
		}catch(SQLException e){}
		conn.zapri();
	}

	public static void posodobitevVodje(int id, String ime, String priimek, String uime, String geslo, String racun, String email, String naslov) {
		String query = "UPDATE Uporabnik SET ime = ?, priimek = ?, uIme = ?, geslo = ?, racun = ?, email = ?, naslov = ?, pravice = ?, sektor = ? WHERE ID = ?;";
		Conn conn = new Conn();
		int pravice = 1;
		int sektor = 0;
		try{
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setString(1, ime);
			ps.setString(2, priimek);
			ps.setString(3, uime);
			ps.setString(4, geslo);
			ps.setString(5, racun);
			ps.setString(6, email);
			ps.setString(7, naslov);
			ps.setInt(8, pravice);
			ps.setInt(9, sektor);
			ps.setInt(10, id);
			ps.executeUpdate();
		}catch(SQLException e){}
		conn.zapri();
	}

	public static void brisiUporabnika(int id) {
		String query = "DELETE FROM Uporabnik WHERE ID = ?;";
		Conn conn = new Conn();
		try{
			PreparedStatement ps = conn.getC().prepareStatement(query);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {}
		conn.zapri();
	}

	public static ArrayList<Skladiscnik> izpisVseSkladiscnike() {
		Conn conn = new Conn();
		ArrayList<Skladiscnik> skladiscniki = new ArrayList<Skladiscnik>();
		String query = "SELECT * FROM Uporabnik WHERE pravice = 2;";
		
		try{
			ResultSet rs = conn.getC().createStatement().executeQuery(query);
			while (rs.next()) {
				Skladiscnik skladiscnik = new Skladiscnik();
				skladiscnik.setID(rs.getInt("ID"));
				skladiscnik.setIme(rs.getString("ime"));
				skladiscnik.setPriimek(rs.getString("priimek"));
				skladiscnik.setUporabniskoIme(rs.getString("uIme"));
				skladiscnik.setGeslo(rs.getString("geslo"));
				skladiscnik.setRacun(rs.getString("racun"));
				skladiscnik.setEmail(rs.getString("email"));
				skladiscnik.setNaslov(rs.getString("naslov"));
				skladiscnik.setPravice(rs.getInt("pravice"));
				skladiscnik.setSektor(rs.getInt("sektor"));
				skladiscniki.add(skladiscnik);
			}
			rs.close();
		}catch (SQLException e) {}
		conn.zapri();
		return skladiscniki;
	}

	public static ArrayList<VodjaSkladisca> izpisiVseVodje() {
		Conn conn = new Conn();
		ArrayList<VodjaSkladisca> vodje = new ArrayList<VodjaSkladisca>();
		String query = "SELECT * FROM Uporabnik WHERE pravice = 1;";
		
		try{
			ResultSet rs = conn.getC().createStatement().executeQuery(query);
			while (rs.next()) {
				VodjaSkladisca vodja = new VodjaSkladisca();
				vodja.setID(rs.getInt("ID"));
				vodja.setIme(rs.getString("ime"));
				vodja.setPriimek(rs.getString("priimek"));
				vodja.setUporabniskoIme(rs.getString("uIme"));
				vodja.setGeslo(rs.getString("geslo"));
				vodja.setRacun(rs.getString("racun"));
				vodja.setEmail(rs.getString("email"));
				vodja.setNaslov(rs.getString("naslov"));
				vodja.setPravice(rs.getInt("pravice"));
				vodja.setSektor(rs.getInt("sektor"));
				vodje.add(vodja);
			}
			rs.close();
		}catch (SQLException e) {}
		conn.zapri();
		return vodje;
	}

	public static ArrayList<Blago> izpisiVsoBlago() {
		Conn conn = new Conn();
		ArrayList<Blago> seznamBlaga = new ArrayList<Blago>();
		String query = "SELECT Blago.ID, Blago.ime, Blago.tip, Blago.kolicina, Blago.enota, Blago.sektor, Blago.dovoljenaKolicina, enotaBlaga.imeEnote, tipBlaga.imeTipa " +
					   "FROM tipBlaga " +
					   "INNER JOIN (enotaBlaga INNER JOIN Blago ON enotaBlaga.[ID] = Blago.[enota]) ON tipBlaga.[ID] = Blago.[tip];";
		try {
			ResultSet rs = conn.getC().createStatement().executeQuery(query);
			while (rs.next()) {
				Blago blago = new Blago();
				blago.setID(rs.getInt("ID"));
				blago.setIme(rs.getString("ime"));
				blago.setTip(rs.getInt("tip"));
				blago.setImeTip(rs.getString("imeTipa"));
				blago.setKolicina(rs.getInt("kolicina"));
				blago.setEnota(rs.getInt("enota"));
				blago.setImeEnota(rs.getString("imeEnote"));
				blago.setSektor(rs.getInt("sektor"));
				blago.setDovoljenaKolicina(rs.getInt("dovoljenaKolicina"));
				
				seznamBlaga.add(blago);
			}
			rs.close();
		} catch (SQLException e) {}
		conn.zapri();
		return seznamBlaga;
	}
	
	public static ArrayList<Blago> izpisiVsoBlagoBySektor(int sektor) {
		Conn conn = new Conn();
		ArrayList<Blago> seznamBlaga = new ArrayList<Blago>();
		String query = "SELECT Blago.ID, Blago.ime, Blago.tip, Blago.kolicina, Blago.enota, Blago.sektor, Blago.dovoljenaKolicina, enotaBlaga.imeEnote, tipBlaga.imeTipa " +
					   "FROM tipBlaga " +
					   "INNER JOIN (enotaBlaga INNER JOIN Blago ON enotaBlaga.[ID] = Blago.[enota]) ON tipBlaga.[ID] = Blago.[tip]" +
					   "WHERE sektor = ?;";
		try {
			PreparedStatement prepStmt = conn.getC().prepareStatement(query);
			prepStmt.setInt(1, sektor);
			ResultSet rs = prepStmt.executeQuery();
			while (rs.next()) {
				Blago blago = new Blago();
				blago.setID(rs.getInt("ID"));
				blago.setIme(rs.getString("ime"));
				blago.setTip(rs.getInt("tip"));
				blago.setImeTip(rs.getString("imeTipa"));
				blago.setKolicina(rs.getInt("kolicina"));
				blago.setEnota(rs.getInt("enota"));
				blago.setImeEnota(rs.getString("imeEnote"));
				blago.setSektor(rs.getInt("sektor"));
				blago.setDovoljenaKolicina(rs.getInt("dovoljenaKolicina"));
				
				seznamBlaga.add(blago);
			}
			rs.close();
		} catch (SQLException e) {}
		conn.zapri();
		return seznamBlaga;
	}
}